

drop table sale;
create table sale(
	sale_no number primary key,
	member_id varchar(18) not null,
	location varchar(30) not null,
	site_name varchar(20) not null,
	product_name varchar(30) not null,
	product_price number not null,
	reduced_price number not null,
	quantity number not null,
	sale_date varchar(14) not null,
	memo varchar(4000) default '',
	product_image varchar(30) default '',
	foreign key (member_id) references member (member_id) on delete cascade
);

create sequence sale_no_seq;
drop sequence sale_no_seq;

drop table wish;
create table wish(
	wish_no number primary key,
	member_id varchar(18) not null,
	product_id		number not null,
	foreign key (member_id) references member (member_id) on delete cascade,
	foreign key (product_id) references product (product_id) on delete cascade
);

create sequence wish_no_seq;
drop sequence wish_no_seq;
select wish_no_seq.nextval from dual;

select * from SALE;
select * from member;
select * from wish;


select product_id,
	    			 product_name , original_price, reduced_price,
	    			 reduced_rate,	read_cnt,	company_id,stock_EA,register_date,	update_date,
	    			 use_start_date,	 use_end_date,			 delete_date,	    			 sale_location,
	    			 product_category, product_url, preference_seq,	  product_image_filename, content 
		from (select ceil(rownum/10) page, product_id,
	    			product_name ,original_price,reduced_price,
	    			reduced_rate,	read_cnt,	company_id,stock_EA,register_date,	update_date,
	    			use_start_date,	use_end_date,			delete_date,	    			sale_location,
	    			product_category,product_url,preference_seq,	 product_image_filename,		content  
		from (select p.product_id,p.product_name ,	p.original_price,			p.reduced_price,
	    			p.reduced_rate,	p.read_cnt,	p.company_id,	p.stock_EA,	p.register_date,
	    			p.update_date,	p.use_start_date,p.use_end_date,	p.delete_date,	p.sale_location,
	    			p.product_category,p.product_url,	p.preference_seq,		p.product_image_filename,
	    			p.content 
	    			from product p, wish w where p.product_id=w.product_id order by p.product_id desc)) 
		where page=1;
		
